﻿''' <summary>
''' This class holds the information regarding a loaded table (and WRDB project if applicable) from which series are created
''' </summary>
Public Class clsTable
    Implements IDisposable

    Friend StationSupport As New Generic.Dictionary(Of String, structStationSupport)
    Friend PCodeSupport As New Generic.Dictionary(Of String, structPCodeSupport)

    Friend Structure structStationSupport
        Dim StationID As String
        Dim Name As String
        Dim RiverMile As Double
        Dim BranchID As String
    End Structure

    Friend Structure structPCodeSupport
        Dim Name As String
        Dim Units As String
        Dim DetectionLimit As Double
        Dim MinCriteria As Double
        Dim MaxCriteria As Double
    End Structure

    Friend Enum enumBMDField
        BranchNum
        RiverMile
    End Enum

    Public Enum enumTableType
        WRDB5
        WRDB4
        Database 'for MDB, XLS, SDB, FDB
        Folder   'for TXT, CSV, DB, DBF
        BMD
        WRDBData  'for file types read using WRDBData class
        Calibration 'special type to hold references to two other tables and mapping
        External  'reference to external data source in server database
    End Enum

    Private _BranchName As New Generic.List(Of String)

    Private _DataSource As String

    Private WithEvents _dsBMD As WRDB.DataSource.clsBMD

    Private _dsData As WRDB.DataSource.clsData

    Private _ProjectName As String

    Private _TableFile As String

    Private _TableName As String

    Private _TableType As enumTableType

    Private _WRDBProject As WRDB.Project.clsProject

    ''' <summary>
    ''' If open .DB file, will export to .TXT for fast operations without BDE; use this flag to identify that case
    ''' </summary>
    Public Property IsConvertedDB As Boolean = False

    ''' <summary>
    ''' If IsConvertedDB, this will store original full path to the DB file
    ''' </summary>
    Public ReadOnly Property OriginalDBName As String
        Get
            Return DataSourceDB & "\" & TableFile.Replace(".TXT", ".DB")
        End Get
    End Property

    ''' <summary>
    ''' If IsConvertedDB, this will store original directly that the DB file was opened from (so can store in settings)
    ''' </summary>
    Private Property DataSourceDB As String = ""

    Friend Property Calibration As clsCalibration

    Friend Property ExternalData As External.clsExternalData

    Friend Property Criteria As WRDB.Project.clsCriteria = Nothing

    ''' <summary>
    ''' Number of values flagged by RCode as not to be computed.
    ''' This value is only value for the last time that GetData was called.
    ''' </summary>
    Friend Property NumNonCalc As Integer

    ''' <summary>
    ''' Number of values flagged by RCode as non-detect.
    ''' This value is only value for the last time that GetData was called.
    ''' </summary>
    Friend Property NumNonDetect As Integer

    ''' <summary>
    ''' Number of values flagged by RCode or Criteria table as exceeding criteria limits.
    ''' This value is only value for the last time that GetData was called.
    ''' </summary>
    Friend Property NumExceed As Integer

    Private _GroupFileName As String
    Private _ExpertMode As Boolean

    ''' <summary>
    ''' If created from queried data in WRDB, this will contain selection string
    ''' </summary>
    ''' <remarks>
    ''' This was implemented January 2011 to get around problem of users querying data but when later open GRAPH file, that temp table no longer exists
    ''' </remarks>
    Friend SqlString As String

    ''' <summary>
    ''' Special "constituent" fields used in the BMD file
    ''' </summary>
    ''' <remarks>Date_Time is always Item 0 in the WRDB data source implementation, others included in array because BMD header doesn't have a place for them</remarks>
    Friend BMDFieldName As String() = {"Branch_Num", "River Mile"}

    ''' <summary>
    ''' The item numbers associated with each special field above (-1 if not found)
    ''' </summary>
    Friend BMDFieldNum As String() = {-1, -1}

    ''' <summary>
    ''' Initializes for BMD files
    ''' </summary>
    ''' <param name="BMDFile">Full path of BMD file</param>
    ''' <returns>True if successful</returns>
    Public Function CreateBMDTable(ByVal BMDFile As String, Optional ByVal RelativePath As String = "") As Boolean
        Try
            Dim BasePath As String = ""
            If _GroupFileName <> "" Then BasePath = IO.Path.GetDirectoryName(_GroupFileName)
            BMDFile = FindFile(BMDFile, BasePath, RelativePath)
            If BMDFile = "" Then Return False

            'Dim ReduceMemory As Boolean = CBool(GetSetting(REGAPP, "General", "BMDMemory", "False"))

            _DataSource = BMDFile
            _TableFile = IO.Path.GetFileName(_DataSource)
            _TableType = enumTableType.BMD
            _ProjectName = "BMD_" & _DataSource 'need to prepend in case there is already a WRDB5 project with the same name
            Try
                _dsBMD = New WRDB.DataSource.clsBMD
                _dsBMD.Initialize(_DataSource, False, , False) 'always open from disk so don't have to read in all concs just to get branches, etc.
            Catch ex As Exception
                Throw New ApplicationException("Error occurred while trying to read BMD file; the file may be corrupt.", ex)
                Return False
            End Try

            Dim SomeMissing As Boolean = False
            For i As enumBMDField = enumBMDField.BranchNum To enumBMDField.RiverMile
                BMDFieldNum(i) = _dsBMD.VarIndex(BMDFieldName(i))
                If BMDFieldNum(i) = -1 Then SomeMissing = True
            Next
            'If SomeMissing Then
            '    Dim s As String = ""
            '    For Each fld As String In VarNames
            '        s &= IIf(s = "", "", ", ") & fld
            '    Next
            '    WarningMsg("The selected table is not a compatible EPDRiv1 BMD file; it must contain the following fields: Branch_Num and River Mile. Here are the field names that were found:\n\n{0}", s)
            '    Return False
            'End If

            'add units to collection
            With _dsBMD
                For i As Integer = 0 To .NumVars - 1
                    If Not IsReservedField(.VarName(i)) Then
                        Dim psupt As structPCodeSupport = Nothing
                        With psupt
                            .Name = ""
                            .Units = _dsBMD.VarUnits(i)
                        End With
                        Dim PCode As String = .VarName(i)
                        'the following would give a unique pcodes, but I'd have to change the way I retrieve the data; for now, just ignore duplicates
                        'Dim Ctr As Integer = 2
                        'While PCodeSupport.ContainsKey(PCode)
                        '    PCode = arPCodes(i) & Ctr
                        '    Ctr += 1
                        'End While
                        If Not PCodeSupport.ContainsKey(PCode) Then PCodeSupport.Add(PCode, psupt)
                    End If
                Next
                For i As Integer = 0 To .NumSegments - 1
                    'for each segment name at first timestep, add associated branch number and rivermile (if applicable)
                    Dim supt As clsTable.structStationSupport
                    supt.StationID = .SegmentName(i)
                    'the following would give a unique pcodes, but I'd have to change the way I retrieve the data; for now, just ignore duplicates
                    'Dim Ctr As Integer = 2
                    'While StationSupport.ContainsKey(supt.StationID)
                    '    supt.StationID = .GetSegNames(i) & Ctr
                    '    Ctr += 1
                    'End While
                    If BMDFieldNum(clsTable.enumBMDField.BranchNum) <> -1 Then
                        Try
                            'supt.BranchID = .GetMinMaxVarSeg(supt.StationID, BMDFieldName(clsTable.enumBMDField.BranchNum))(0)
                            Dim v As Integer = .VarIndex(BMDFieldName(clsTable.enumBMDField.BranchNum))
                            If v = -1 Then Return False
                            supt.BranchID = .Conc(BMDFieldName(clsTable.enumBMDField.BranchNum), supt.StationID, 0)
                        Catch ex As Exception
                            supt.BranchID = -1
                        End Try
                    Else
                        supt.BranchID = -1
                    End If
                    If BMDFieldNum(clsTable.enumBMDField.RiverMile) <> -1 Then
                        Try
                            'supt.RiverMile = .GetMinMaxVarSeg(supt.StationID, BMDFieldName(clsTable.enumBMDField.RiverMile))(0)
                            'supt.RiverMile = .GetConc0(supt.StationID, BMDFieldName(clsTable.enumBMDField.RiverMile))
                            Dim v As Integer = .VarIndex(BMDFieldName(clsTable.enumBMDField.RiverMile))
                            If v = -1 Then Return False
                            supt.RiverMile = .Conc(BMDFieldName(clsTable.enumBMDField.RiverMile), supt.StationID, 0)
                        Catch ex As Exception
                            supt.RiverMile = -1
                        End Try
                    Else
                        supt.RiverMile = -1
                    End If
                    supt.Name = String.Format("Nd={0}", i + 1)
                    If supt.RiverMile <> -1 Then supt.Name &= String.Format("; RM={0:N2}", supt.RiverMile)
                    If Not StationSupport.ContainsKey(supt.StationID) Then StationSupport.Add(supt.StationID, supt)
                    '.InRam = Not ReduceMemory
                Next
                .InRam = True 'try to store it all in ram if possible
            End With
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Create temporary WRDB5 project that points to selected table (can only access file-based databases)
    ''' </summary>
    ''' <param name="DataSource">Name of embedded database file or folder</param>
    ''' <param name="TableFileName">Name of selected file (with extension and no path) or table if embedded in database</param>
    ''' <returns>True if successful</returns>
    Public Function CreateDatabaseTable(ByRef DataSource As String, ByRef TableFileName As String, Optional ByVal RelativePath As String = "") As Boolean
        Try
            'see if datasource is database file (like .mdb); otherwise is a directory name 
            Dim BasePath As String = ""
            If _GroupFileName <> "" Then BasePath = IO.Path.GetDirectoryName(_GroupFileName)

            Dim ext As String = IO.Path.GetExtension(DataSource)
            Select Case ext.ToUpper
                'if datasource to has recognized extension, assume that it is a filename rather than directory
                Case ".MDB", ".XLS", ".XLSX", ".XLSM", ".SDB", ".FDB", ".SDF"
                    DataSource = FindFile(DataSource, BasePath, RelativePath)
                    If DataSource = "" Then Return False
                Case Else
                    Dim fn As String = FindFile(DataSource & "\" & TableFileName, BasePath, RelativePath)
                    If fn = "" Then Return False
                    DataSource = IO.Path.GetDirectoryName(fn)
                    TableFileName = IO.Path.GetFileName(fn)
            End Select

            _TableFile = TableFileName
            _DataSource = DataSource
            Dim dbType As clsDB.enumDBType
            ext = IO.Path.GetExtension(DataSource).ToUpper
            If TableFileName.Contains(".") Then ext = IO.Path.GetExtension(TableFileName).ToUpper

            If ext = ".DB" Then
                'want to avoid need for BDE; export to .TXT file and use that as database
                Dim TxtFile As String = WRDB.Project.clsProject.TempPath & "\" & IO.Path.ChangeExtension(TableFileName, ".TXT")
                'if same named .TXT file already exists in directory, just use that one
                If Not My.Computer.FileSystem.FileExists(TxtFile) OrElse FileIO.FileSystem.GetFileInfo(_DataSource & "\" & TableFileName).LastWriteTime > FileIO.FileSystem.GetFileInfo(TxtFile).LastWriteTime Then
                    If Not WRDB.DBProviders.clsDBParadox.ExportToText(_DataSource & "\" & TableFileName, TxtFile) Then Return False
                End If
                IsConvertedDB = True
                DataSourceDB = _DataSource
                _DataSource = WRDB.Project.clsProject.TempPath
                _TableFile = IO.Path.ChangeExtension(TableFileName, ".TXT")
                ext = ".TXT"
            End If

            Select Case ext
                Case ".DB", ".DBF"
                    If False And Is64BitOperatingSystem() Then
                        WarningMsg("You are running a 64-bit version of Windows and will not be able to open this file type; import the file to your WRDB 5 project first.")
                        Return False
                    End If
                    If Not _DataSource.EndsWith("\") Then _DataSource &= "\"
                    _TableType = enumTableType.Folder
                    _TableName = "[" & IO.Path.GetFileNameWithoutExtension(_TableFile) & "]"
                    Select Case ext
                        Case ".DB" : dbType = clsDB.enumDBType.Paradox
                        Case ".DBF" : dbType = clsDB.enumDBType.DBase
                    End Select
                Case ".TXT", ".CSV"
                    If Not _DataSource.EndsWith("\") Then _DataSource &= "\"
                    _TableType = enumTableType.Folder
                    _TableName = "[" & _TableFile.Replace(".", "#") & "]"
                    Select Case ext
                        Case ".TXT" : dbType = clsDB.enumDBType.Text
                        Case ".CSV" : dbType = clsDB.enumDBType.CSV
                    End Select
                Case ".MDB", ".XLS", ".XLSX", ".XLSM", ".SDB", ".FDB", ".SDF"
                    _TableType = enumTableType.Database
                    _TableName = _TableFile
                    Select Case ext
                        Case ".MDB" : dbType = clsDB.enumDBType.Access
                        Case ".XLS", ".XLSX", ".XLSM" : dbType = clsDB.enumDBType.Excel
                        Case ".SDB" : dbType = clsDB.enumDBType.SQLite
                        Case ".FDB" : dbType = clsDB.enumDBType.Firebird_Embedded
                        Case ".SDF" : dbType = clsDB.enumDBType.SQL_Server_CE
                    End Select
            End Select

            _ProjectName = "OTHER_" & _DataSource 'need to prepend in case there is already a WRDB5 project with the same name
            _WRDBProject = New WRDB.Project.clsProject(_ProjectName)
            Try
                If _WRDBProject.CreateTempProject(dbType, _DataSource) Then
                    If _TableName <> "" Then
                        Dim colList As Generic.List(Of String) = _WRDBProject.DB.GetColumnList(_TableName)
                        If Not (colList.Contains("STATION_ID") AndAlso colList.Contains("PCODE") AndAlso colList.Contains("RESULT") AndAlso colList.Contains("RCODE") AndAlso colList.Contains("DATE_TIME")) Then
                            Dim s As String = ""
                            For Each fld As String In colList
                                s &= IIf(s = "", "", ", ") & fld
                            Next
                            WarningMsg("The selected table is not a compatible import table; it must contain the following fields: Station_ID, PCode, Result, RCode, and Date_Time. Here are the field names that were found:\n\n{0}", s)
                            Return False
                        End If
                    End If
                    If dbType = clsDB.enumDBType.Paradox Then
                        With _WRDBProject
                            .WorkingDir = _DataSource
                            .MasterDir = _DataSource
                            .SupportDir = _DataSource
                        End With
                    End If
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                If WarningMsg("Unable to load and/or initialize the appropriate WRDB data provider library for {0}; you must install WRDB and run it at least once so that the proper data provider libraries can be found.\n\nThe detailed error message was:\n\n{1}\n\nDo you want to browse to the WRDB web site now?", dbType.ToString, ex.Message) = DialogResult.OK Then
                    Process.Start("http://www.wrdb.com")
                End If
                Return False
            End Try

        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Initializes for reading of WRDBData files
    ''' </summary>
    ''' <param name="DataFile">Full path of data file</param>
    ''' <returns>True if successful</returns>
    Public Function CreateWRDBDataTable(ByVal DataFile As String, Optional ByVal RelativePath As String = "") As Boolean
        Try
            Dim BasePath As String = ""
            If _GroupFileName <> "" Then BasePath = IO.Path.GetDirectoryName(_GroupFileName)
            DataFile = FindFile(DataFile, BasePath, RelativePath)
            If DataFile = "" Then Return False

            _DataSource = DataFile
            TableFile = IO.Path.GetFileName(DataFile)
            _TableName = IO.Path.GetFileName(DataFile)
            _TableType = enumTableType.WRDBData
            _ProjectName = "DATA_" & _DataSource 'need to prepend in case there is already a WRDB5 project with the same name
            _dsData = WRDB.DataSource.clsData.Instantiate(DataFile)
            If _dsData Is Nothing Then Return False

            'add units to collection
            With _dsData
                For Each s As String In .FieldNames
                    Dim psupt As structPCodeSupport = Nothing
                    With psupt
                        Dim j As Integer = s.IndexOf(" ")
                        Dim k As Integer = s.LastIndexOf("(")
                        Dim PCode As String = ""
                        If j <> -1 Then
                            PCode = s.Substring(0, j).Trim
                            If k <> -1 Then
                                .Name = s.Substring(j, k - j).Trim
                                .Units = s.Substring(k + 1).Replace(")", "").Trim
                            Else
                                .Name = s.Substring(j).Trim
                                .Units = ""
                            End If
                        Else
                            PCode = s.Trim
                            .Name = ""
                            .Units = ""
                        End If
                        If Not PCodeSupport.ContainsKey(PCode) Then PCodeSupport.Add(PCode, psupt)
                    End With
                Next
                For Each s As String In .StationIDs
                    Dim supt As clsTable.structStationSupport
                    supt.StationID = s
                    supt.BranchID = -1
                    supt.RiverMile = -1
                    supt.Name = ""
                    If Not StationSupport.ContainsKey(supt.StationID) Then StationSupport.Add(supt.StationID, supt)
                Next
            End With

            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Create temporary WRDB5 project that points to WRDB 4.x (Paradox) project
    ''' </summary>
    ''' <param name="WRDB4ProjectName">Name of WRDB 4.x project</param>
    ''' <returns>True if successful</returns>
    Public Function CreateWRDB4Table(ByVal WRDB4ProjectName As String) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            _DataSource = WRDB4ProjectName
            _ProjectName = "WRDB4_" & WRDB4ProjectName 'need to prepend in case there is already a WRDB5 project with the same name
            Dim prjDir As String = System.Environment.GetFolderPath(Environment.SpecialFolder.System)
            prjDir = prjDir.Substring(0, prjDir.LastIndexOf("\"))
            Dim prjFile As New WRDB.IniFile.clsIniFile(prjDir & "\" & WRDB4ProjectName & ".prj", False)
            With prjFile
                Dim WorkingDir As String = .GetKeyText("Settings", "TempPath")
                Dim MasterDir As String = .GetKeyText("Settings", "MasterPath")
                Dim SupportDir As String = .GetKeyText("Settings", "SupportPath")
                Dim SupportDirProject As String = .GetKeyText("Settings", "ProjectSupportPath")
                _WRDBProject = New WRDB.Project.clsProject(_ProjectName)
                _TableType = enumTableType.WRDB4
                If _WRDBProject.CreateTempProject(WorkingDir, MasterDir, SupportDir, SupportDirProject) Then
                    If _WRDBProject.DB Is Nothing Then Return False
                    If Not _WRDBProject.ResetPDox("PCODES") Then Return False
                    Dim PSupt As structPCodeSupport = Nothing
                    dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode,Anal_Name,Units,Det_Limit,Min_Criteria,Max_Criteria FROM PCodes")
                    While dr IsNot Nothing AndAlso dr.Read
                        Dim PCode As String = ""
                        With PSupt
                            PCode = TestNull(dr.GetValue(0), "")
                            .Name = TestNull(dr.GetValue(1), "")
                            .Units = TestNull(dr.GetValue(2), "")
                            .DetectionLimit = TestNull(dr.GetValue(3), Double.MaxValue)
                            .MinCriteria = TestNull(dr.GetValue(4), Double.MinValue)
                            .MaxCriteria = TestNull(dr.GetValue(5), Double.MaxValue)
                        End With
                        If PCode <> "" Then PCodeSupport.Add(PCode, PSupt)
                    End While
                    dr.Close()

                    Dim supt As clsTable.structStationSupport
                    _WRDBProject.ResetPDox("Stations")
                    dr = _WRDBProject.DB.ExecuteReader("SELECT Station_ID,Station_Name,Branch_ID,River_Mile FROM Stations")
                    While dr IsNot Nothing AndAlso dr.Read AndAlso Not IsDBNull(0)
                        supt.StationID = dr.GetString(0)
                        supt.Name = TestNull(dr.GetString(1), "")
                        supt.BranchID = TestNull(dr.GetString(2), "")
                        supt.RiverMile = TestNull(dr.GetValue(3), 0)
                        StationSupport.Add(supt.StationID, supt)
                    End While
                    dr.Close()

                    Return True
                Else
                    Return False
                End If
            End With
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing AndAlso _WRDBProject.DB IsNot Nothing Then _WRDBProject.DB.CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Get reference to existing WRDB5 project
    ''' </summary>
    ''' <param name="WRDB5ProjectName">Name of WRDB 5.0 project</param>
    ''' <returns>True if successful</returns>
    Public Function CreateWRDB5Table(ByVal WRDB5ProjectName As String) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            _DataSource = WRDB5ProjectName
            _ProjectName = WRDB5ProjectName
            _WRDBProject = New Project.clsProject(_ProjectName)
            If _WRDBProject.DB Is Nothing Then Return False

            _TableType = enumTableType.WRDB5

            If Not _WRDBProject.ResetPDox("PCODES") Then Return False
            Dim PSupt As structPCodeSupport = Nothing
            dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode,Anal_Name,Units,Det_Limit,Min_Criteria,Max_Criteria FROM PCodes")
            While dr IsNot Nothing AndAlso dr.Read
                Dim PCode As String = ""
                With PSupt
                    PCode = TestNull(dr.GetValue(0), "")
                    .Name = TestNull(dr.GetValue(1), "")
                    .Units = TestNull(dr.GetValue(2), "")
                    .DetectionLimit = TestNull(dr.GetValue(3), Double.MaxValue)
                    .MinCriteria = TestNull(dr.GetValue(4), Double.MinValue)
                    .MaxCriteria = TestNull(dr.GetValue(5), Double.MaxValue)
                End With
                If PCode <> "" Then PCodeSupport.Add(PCode, PSupt)
            End While
            dr.Close()

            Dim supt As clsTable.structStationSupport
            _WRDBProject.ResetPDox("Stations")
            dr = _WRDBProject.DB.ExecuteReader("SELECT Station_ID,Station_Name,Branch_ID,River_Mile FROM Stations")
            While dr IsNot Nothing AndAlso dr.Read
                If Not dr.IsDBNull(0) Then
                    supt.StationID = dr.GetString(0)
                    supt.Name = TestNull(dr.GetValue(1), "")
                    supt.BranchID = TestNull(dr.GetValue(2), "")
                    supt.RiverMile = TestNull(dr.GetValue(3), 0)
                    StationSupport.Add(supt.StationID, supt)
                End If
            End While
            dr.Close()

            Return True
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return False
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing AndAlso _WRDBProject.DB IsNot Nothing Then _WRDBProject.DB.CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Get reference to newly created temporary WRDB5 project
    ''' </summary>
    ''' <param name="WRDB5Project">Temporary WRDB 5.0 project</param>
    ''' <returns>True if successful</returns>
    Public Function CreateWRDB5Table(WRDB5Project As Project.clsProject) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            _DataSource = WRDB5Project.Name
            _ProjectName = WRDB5Project.Name
            _WRDBProject = WRDB5Project
            If _WRDBProject.DB Is Nothing Then Return False

            _TableType = enumTableType.WRDB5

            PCodeSupport.Clear()
            If _WRDBProject.DB.TableExists("PCodes") Then
                Dim PSupt As structPCodeSupport = Nothing
                dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode,Anal_Name,Units,Det_Limit,Min_Criteria,Max_Criteria FROM PCodes")
                While dr IsNot Nothing AndAlso dr.Read
                    Dim PCode As String = ""
                    With PSupt
                        PCode = TestNull(dr.GetValue(0), "")
                        .Name = TestNull(dr.GetValue(1), "")
                        .Units = TestNull(dr.GetValue(2), "")
                        .DetectionLimit = TestNull(dr.GetValue(3), Double.MaxValue)
                        .MinCriteria = TestNull(dr.GetValue(4), Double.MinValue)
                        .MaxCriteria = TestNull(dr.GetValue(5), Double.MaxValue)
                    End With
                    If PCode <> "" Then PCodeSupport.Add(PCode, PSupt)
                End While
                dr.Close()
            End If

            StationSupport.Clear()
            If _WRDBProject.DB.TableExists("Stations") Then
                Dim supt As clsTable.structStationSupport
                dr = _WRDBProject.DB.ExecuteReader("SELECT Station_ID,Station_Name,Branch_ID,River_Mile FROM Stations")
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then
                        supt.StationID = dr.GetString(0)
                        supt.Name = TestNull(dr.GetValue(1), "")
                        supt.BranchID = TestNull(dr.GetValue(2), "")
                        supt.RiverMile = TestNull(dr.GetValue(3), 0)
                        StationSupport.Add(supt.StationID, supt)
                    End If
                End While
                dr.Close()
            End If
            Return True
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return False
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing AndAlso _WRDBProject.DB IsNot Nothing Then _WRDBProject.DB.CloseConnection()
        End Try
    End Function

    ' ''' <summary>
    ' ''' Given external database connection name and table, import data into temporary SQLite project database
    ' ''' </summary>
    ' ''' <param name="ConnectionName">Name of external connection</param>
    ' ''' <param name="TableConnectionName">Name of external table connection</param>
    ' ''' <remarks></remarks>
    'Public Function CreateExternalTable(ByVal ConnectionName As String, ByVal TableConnectionName As String) As Boolean
    '    'based in FileOpenConnection in WRDB frmMain
    '    Dim dr As DbDataReader = Nothing
    '    Dim DataTable As Project.clsDataTable = Nothing
    '    Dim DB As clsDB = Nothing
    '    Try

    '        'always create a SQLite WRDB temporary project to hold data imported from external database
    '        Dim Project As New WRDB.Project.clsProject("External", True)
    '        If Not Project.CreateTempProject(clsDB.enumDBType.SQLite, WRDB.Project.clsProject.TempPath & "\External.sdb") Then WarningMsg("Unable to create temporary WRDB project to hold external data.") : Exit Function

    '        Dim NewTableName As String = ConnectionName & "_" & TableConnectionName
    '        NewTableName = Project.DB.GetValidTableName(NewTableName).Replace(" ", "_")

    '        Dim IniFile As New Project.clsWRDBIniFile
    '        With IniFile.ConnectionInfo(ConnectionName)
    '            DB = clsDB.GetDataProvider(.DatabaseType, .ServerAddress, .DatabaseName, .UserName, .Password, 0)
    '        End With

    '        With IniFile.TableMappingInfo(ConnectionName, TableConnectionName)

    '        End With

    '        Dim NumErrors As Integer = 0

    '        WriteProgress(GraphForm, "Acquiring external data...")

    '        Project.DB.DropTable(NewTableName)

    '        'make dictionary to facilitate record insertion
    '        Dim dictFields As New Generic.Dictionary(Of String, String)

    '        'form lists for sql statement based on selections on form

    '        Dim arWRDBFields() As String = {"Station_ID", "PCode", "Date_Time", "Result", "RCode", "CCode", "Agency", "LEW_Pct", "Depth_M", "Date", "Time"}

    '        If alFieldNames Is Nothing Then Exit Function

    '        Dim fldlist As String = ""
    '        Dim DateField As String = ""
    '        For r As Integer = 0 To arWRDBFields.Length - 1
    '            If r <= alFieldNames.Count - 1 AndAlso alFieldNames(r) <> "(None)" Then
    '                Dim CastType As DBProviders.clsDB.enumCastType = clsDB.enumCastType.String_
    '                Select Case r
    '                    Case 2, 15
    '                        CastType = clsDB.enumCastType.DateTime
    '                    Case 3, 7, 8
    '                        CastType = clsDB.enumCastType.Number
    '                    Case 14
    '                        CastType = clsDB.enumCastType.Boolean_
    '                    Case 16
    '                        CastType = clsDB.enumCastType.Integer_
    '                    Case 9 'Date part; save the field name and expect time to be present
    '                        DateField = alFieldNames(r)
    '                        Continue For
    '                    Case 10 'Time part; concatenate date and time to form date-time field
    '                        'this was designed to work with ACP BNA tables, which have the date in a date-time field and the time in a 5 character date field (HH:MM)
    '                        'normally, could just convert to string, concatenate, and then convert back to date, but they have times expressed as 24:00 which
    '                        'is invalid SQL. therefore must use SQLServer DATEADD function!
    '                        If DB.DatabaseType <> clsDB.enumDBType.SQL_Server Then
    '                            WarningMsg("Combine separate date and time fields is only possible when using the SQL Server database type.")
    '                            Exit Function
    '                        End If
    '                        If DateField = "(None)" Then WarningMsg("If you select a field for the time, you must also select a field for the date.") : Exit Function
    '                        Dim TimeField As String = alFieldNames(r)
    '                        alFieldNames(r) = String.Format("CONVERT(DATETIME, " &
    '                                                        "CASE WHEN ISNUMERIC(SUBSTRING({0},1,2))=1 AND ISNUMERIC(SUBSTRING({0},4,2))=1 THEN " &
    '                                                        "DATEADD(HOUR,  CONVERT(INT,SUBSTRING({0},1,2)), " &
    '                                                        "DATEADD(MINUTE,CONVERT(INT,SUBSTRING({0},4,2)), {1}))" &
    '                                                        "ELSE " &
    '                                                        "{1} " &
    '                                                        "END)",
    '                                                        TimeField, DateField)
    '                        fldlist &= String.Format("{0} {1} AS {2}", IIf(fldlist = "", "", ","), alFieldNames(r), arWRDBFields(2))
    '                        dictFields.Add(arWRDBFields(2), alFieldNames(r))
    '                        Continue For
    '                End Select
    '                fldlist &= String.Format("{0} {1} AS {2}", IIf(fldlist = "", "", ","), Project.DB.CastAs(alFieldNames(r), CastType), arWRDBFields(r))
    '                dictFields.Add(arWRDBFields(r), alFieldNames(r))

    '            ElseIf (r = 0 Or r = 1) AndAlso alFieldNames(r) = "(None)" Then
    '                'assigned none to station id and/or pcode; always substitute specified value (stored as only value in array list)
    '                Dim constval As String = ""
    '                If r = 0 Then constval = alStations(0) Else constval = alParameters(0)
    '                fldlist &= String.Format("{0} '{1}' AS {2}", IIf(fldlist = "", "", ","), constval, arWRDBFields(r))

    '            ElseIf r <> 2 And r <> 9 And r <> 10 Then
    '                fldlist &= String.Format("{0} NULL AS {1}", IIf(fldlist = "", "", ","), arWRDBFields(r))
    '            End If
    '        Next

    '        Dim WhereSta As String = ""
    '        With alStations
    '            If dictFields.ContainsKey(arWRDBFields(0)) AndAlso alStations IsNot Nothing AndAlso .Count > 0 Then
    '                Dim list As String = ""
    '                For i As Integer = 0 To .Count - 1
    '                    list &= IIf(list = "", "", ",") & "'" & .Item(i) & "'"
    '                Next
    '                WhereSta = String.Format("Dta.{0} IN ({1})", arWRDBFields(0), list)
    '            End If
    '        End With

    '        Dim WherePCode As String = ""
    '        With alParameters
    '            If dictFields.ContainsKey(arWRDBFields(0)) AndAlso alParameters IsNot Nothing AndAlso .Count > 0 Then
    '                Dim list As String = ""
    '                For i As Integer = 0 To .Count - 1
    '                    list &= IIf(list = "", "", ",") & "'" & .Item(i) & "'"
    '                Next
    '                WherePCode = String.Format("Dta.{0} IN ({1})", arWRDBFields(1), list)
    '            End If
    '        End With

    '        Dim WhereDate As String = ""
    '        If MinDate <> Date.MinValue And MaxDate <> Date.MaxValue Then
    '            WhereDate = String.Format("Dta.{0} BETWEEN {1} AND {2}", arWRDBFields(2), DB.DateTime(MinDate), DB.DateTime(MaxDate))
    '        End If

    '        Dim Where As String = ""
    '        If WhereSta <> "" Then Where &= IIf(Where = "", "", " AND ") & WhereSta
    '        If WherePCode <> "" Then Where &= IIf(Where = "", "", " AND ") & WherePCode
    '        If WhereDate <> "" Then Where &= IIf(Where = "", "", " AND ") & WhereDate
    '        If Where <> "" Then Where = "WHERE " & Where

    '        Dim sqlSelect As String = String.Format("SELECT Station_ID,PCode,Date_Time,Result,RCode,CCode,Agency,LEW_Pct,Depth_M FROM (SELECT {0} FROM {1}) Dta {2}", fldlist, Project.DB.AddBrackets(ExtTableName), Where)
    '        dr = DB.ExecuteReader(sqlSelect)

    '        Dim RecNum As Integer = 0

    '        DataTable = New WRDB.Project.clsDataTable(Project, NewTableName)

    '        While dr IsNot Nothing AndAlso dr.Read
    '            Dim dtaRcd As New WRDB.Project.clsDataRecord
    '            For i As Integer = 0 To 8
    '                Select Case i
    '                    Case 0 : dtaRcd.StationID = dr.GetValue(i)
    '                    Case 1 : dtaRcd.PCode = dr.GetValue(i)
    '                    Case 2 : dtaRcd.DateTime = dr.GetValue(i)
    '                    Case 3 : dtaRcd.Result = dr.GetValue(i)
    '                    Case 4 : dtaRcd.RCode = dr.GetValue(i)
    '                    Case 5 : dtaRcd.CCode = dr.GetValue(i)
    '                    Case 6 : dtaRcd.Agency = dr.GetValue(i)
    '                    Case 7 : dtaRcd.LEWPct = dr.GetValue(i)
    '                    Case 8 : dtaRcd.DepthM = dr.GetValue(i)
    '                End Select
    '            Next
    '            DataTable.InsertRecordFast(dtaRcd)
    '            RecNum += 1
    '            If RecNum Mod 5000 = 0 Then
    '                WriteProgress(GraphForm, "Reading data ({0} records so far)", RecNum)
    '            End If
    '        End While

    '        WriteProgress(GraphForm, "Updating table and closing (please be patient, it may take some time)")
    '        DataTable.Close(WRDB.Project.clsDataTable.enumEndTransaction.Commit, False)

    '        'now the external data have been imported to Graph database into temporary table
    '        'create a temporary table and then show the Manage Series form and allow user to add series from it

    '        Project.Name = ConnectionName
    '        CreateWRDB5Table(Project)
    '        TableName = NewTableName
    '        Return True
    '    Catch ex As Exception
    '        If DataTable IsNot Nothing Then DataTable.Close(Project.clsDataTable.enumEndTransaction.Rollback)
    '        ErrorMsg(, ex)
    '        Return False
    '    Finally
    '        If dr IsNot Nothing Then dr.Close()
    '        If DB IsNot Nothing Then DB.Dispose()
    '        WriteProgress(GraphForm)
    '    End Try
    'End Function

    ''' <summary>
    ''' Given external database connection name and table, import data into temporary SQLite project database
    ''' </summary>
    ''' <param name="ConnectionName">Name of external connection</param>
    ''' <param name="TableConnectionName">Name of external table connection</param>
    ''' <remarks></remarks>
    Public Function CreateExternalTable(ByVal ConnectionName As String, ByVal TableConnectionName As String) As Boolean
        Dim IniFile As New WRDB.Project.clsWRDBIniFile
        ExternalData = New External.clsExternalData(IniFile, Nothing, ConnectionName, TableConnectionName)
        With ExternalData
            Dim NewProject As WRDB.Project.clsProject = Nothing
            Dim NewTableName As String = ""
            If .GetData(NewProject, NewTableName) Then
                CreateWRDB5Table(NewProject)
                _TableType = enumTableType.External
                TableName = NewTableName
                Return True
            Else
                Return False
            End If
        End With
    End Function

    ''' <summary>
    ''' Initialize calibration table that actually refers to two other tables
    ''' </summary>
    ''' <param name="Calib">Calibration object</param>
    ''' <returns>True if successful</returns>
    Public Function CreateCalibrationTable(Calib As clsCalibration) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            _Calibration = Calib.Clone

            _TableName = Calib.Name
            _TableType = enumTableType.Calibration

            With Calibration
                For Each b As clsBranch In .dictBranches.Values
                    For Each sta As clsStation In b.dictStations.Values
                        For Each parm As clsParameter In sta.dictParameters.Values
                            For Each staID As String In parm.lstStationsMeas
                                Dim SSupt As structStationSupport = Nothing
                                If .tblMeasured.StationSupport.ContainsKey(staID) Then
                                    SSupt = .tblMeasured.StationSupport(staID)
                                Else
                                    SSupt.StationID = sta.Name
                                    SSupt.Name = sta.Name
                                    SSupt.RiverMile = sta.Length
                                    SSupt.BranchID = b.Name
                                End If
                                If Not StationSupport.ContainsKey(sta.Name) Then
                                    StationSupport.Add(sta.Name, SSupt)
                                End If
                            Next
                            For Each pcode As String In parm.lstParametersMeas
                                Dim PSupt As structPCodeSupport = Nothing
                                If .tblMeasured.PCodeSupport.ContainsKey(pcode) Then
                                    PSupt = .tblMeasured.PCodeSupport(pcode)
                                Else
                                    PSupt.Name = parm.Name
                                    PSupt.Units = ""
                                    PSupt.MinCriteria = Double.MinValue
                                    PSupt.MaxCriteria = Double.MaxValue
                                    PSupt.DetectionLimit = 0
                                End If
                                If Not PCodeSupport.ContainsKey(parm.Name) Then
                                    PCodeSupport.Add(parm.Name, PSupt)
                                End If
                            Next
                        Next
                    Next
                Next
            End With

            Return True
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return False
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Determines if current table is same as another
    ''' </summary>
    ''' <param name="tbl">Table to compare to</param>
    Public Overloads Function Equals(ByVal tbl As clsTable) As Boolean
        Return _DataSource = tbl.DataSource And _TableName = tbl.TableName
    End Function

    Private Function AxisLabel(ByVal PCode As String) As String
        If PCodeSupport.ContainsKey(PCode) Then
            With PCodeSupport(PCode)
                Return String.Format("{0}: {1} {2}", PCode, .Name.Trim, IIf(.Units.Trim = "", "", "(" & .Units.Trim & ")"))
            End With
        Else
            Return PCode
        End If
    End Function

    ''' <summary>
    ''' Is valid WRDB data table or EPDRiv1 BMD file
    ''' </summary>
    ''' <returns>True if valid</returns>
    Friend Function GetBranchList() As Generic.List(Of String)
        Dim lstBranches As New Generic.List(Of String)
        lstBranches.Add("All Branches")

        If _TableType = enumTableType.BMD AndAlso BMDFieldNum(enumBMDField.BranchNum) <> -1 Then
            With _dsBMD
                For i As Integer = 0 To .NumSegments - 1
                    Dim BranchNum As Integer = 0
                    BranchNum = .Conc(BMDFieldName(enumBMDField.BranchNum), .SegmentName(i), 0)
                    Dim s As String
                    If _BranchName.Count = 0 Then
                        s = BranchNum
                    Else
                        s = String.Format("{0} - {1}", BranchNum, _BranchName(BranchNum - 1))
                    End If
                    If Not lstBranches.Contains(s) Then lstBranches.Add(s)
                Next
            End With

        ElseIf _TableType = enumTableType.Calibration Then
            With Calibration
                For Each b As String In .dictBranches.Keys
                    lstBranches.Add(b)
                Next
            End With

        ElseIf _TableType = enumTableType.WRDB4 Or _TableType = enumTableType.WRDB5 Then

            'try to select branches from stations table
            Dim dr As DbDataReader = Nothing
            Try
                With _WRDBProject
                    If .DB.TableExists("Stations") Then
                        dr = .DB.ExecuteReader("SELECT DISTINCT Branch_ID FROM Stations,{0} Dta WHERE Dta.Station_ID=Stations.Station_ID", _TableName)
                        'following is much faster, but some providers place a limit on the number of stations allowed
                        'dr = .DB.ExecuteReader("SELECT DISTINCT Branch_ID FROM Stations WHERE Station_ID IN ({0})", .DB.ArrayToCSV(.DB.GetRecordList(_TableName, "Station_ID"), True))
                        While dr IsNot Nothing AndAlso dr.Read
                            If Not dr.IsDBNull(0) Then lstBranches.Add(dr.GetString(0))
                        End While
                        dr.Close()
                        dr = Nothing
                    End If
                End With
            Catch ex As Exception
            Finally
                If dr IsNot Nothing Then dr.Close()
                _WRDBProject.DB.CloseConnection()
            End Try
        End If
        Return lstBranches
    End Function

    ''' <summary>
    ''' Get list of pcodes associated with all specified station id
    ''' </summary>
    ''' <param name="StationID">Station ID, OK if followed by station Name (if blank, will return all PCodes)</param>
    Friend Function GetPCodeList(ByVal StationID As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            Dim lstPCodes As New Generic.List(Of String)
            If _TableType = enumTableType.BMD Then
                With _dsBMD
                    For i As Integer = 0 To .NumVars - 1
                        If Not IsReservedField(.VarName(i)) Then lstPCodes.Add(.VarName(i))
                    Next
                End With
            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    For i As Integer = 2 To .FieldNames.Count - 1
                        lstPCodes.Add(.FieldNames(i).Split(" ")(0))
                    Next
                End With
            ElseIf _TableType = enumTableType.Calibration Then
                For Each b As clsBranch In Calibration.dictBranches.Values
                    For Each sta As clsStation In b.dictStations.Values
                        If sta.Name = StationID Or StationID = "" Then
                            For Each parm As String In sta.dictParameters.Keys
                                If Not lstPCodes.Contains(parm) Then lstPCodes.Add(parm)
                            Next
                        End If
                    Next
                Next
            Else
                If StationID = "" Then
                    dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode FROM {0} ORDER BY PCode", _TableName)
                Else
                    dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode FROM {0} WHERE Station_ID='{1}' ORDER BY PCode", _TableName, StringSplit(StationID, " - ")(0))
                End If

                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then lstPCodes.Add(dr.GetString(0))
                End While
            End If
            Return lstPCodes
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Get list of pcodes associated with all specified station ids
    ''' </summary>
    Friend Function GetPCodeList(ByVal StationIDs() As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            Dim lstPCodes As New Generic.List(Of String)
            If _TableType = enumTableType.BMD Then
                With _dsBMD
                    For i As Integer = 0 To .NumVars - 1
                        If Not IsReservedField(.VarName(i)) Then lstPCodes.Add(.VarName(i))
                    Next
                End With
            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    lstPCodes = .PCodes
                End With
            ElseIf _TableType = enumTableType.Calibration Then
                Dim lstStationIDs As New Generic.List(Of String)
                lstStationIDs.AddRange(StationIDs)
                For Each b As clsBranch In Calibration.dictBranches.Values
                    For Each sta As clsStation In b.dictStations.Values
                        If lstStationIDs.Contains(sta.Name) Then
                            For Each parm As String In sta.dictParameters.Keys
                                If Not lstPCodes.Contains(parm) Then lstPCodes.Add(parm)
                            Next
                        End If
                    Next
                Next
            Else
                Dim stalist As String = ""
                For Each s As String In StationIDs
                    stalist &= IIf(stalist = "", "", ",") & "'" & StringSplit(s, " - ")(0) & "'"
                Next
                dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT PCode FROM {0} WHERE Station_ID IN ({1}) ORDER BY PCode", _WRDBProject.DB.AddBrackets(_TableName), stalist)

                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then lstPCodes.Add(dr.GetString(0))
                End While
            End If
            Return lstPCodes
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Get list of stations in this table associated with specified branch (or all branches) formatted like StationID - StationName
    ''' </summary>
    Friend Function GetStationList(ByVal BranchName As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Dim lstStations As New Generic.List(Of String)

            Select Case _TableType
                Case enumTableType.BMD
                    'each segment is treated like a different table
                    With _dsBMD
                        If BranchName = "All Branches" Or BranchName = "" Then
                            For i As Integer = 0 To .NumSegments - 1
                                Dim ID As String = .SegmentName(i)
                                lstStations.Add(String.Format("{0} - {1}", ID, StationSupport(ID).Name))
                            Next
                        ElseIf BMDFieldNum(enumBMDField.BranchNum) <> -1 Then
                            For i As Integer = 0 To .NumSegments - 1
                                Dim SegName As String = .SegmentName(i)
                                Dim BranchNum As Integer = 0
                                Try
                                    BranchNum = .Conc(BMDFieldName(enumBMDField.BranchNum), SegName, 0)
                                Catch ex As Exception
                                End Try
                                If BranchNum = Val(BranchName) Then lstStations.Add(String.Format("{0} - {1}", SegName, StationSupport(SegName).Name))
                            Next
                        End If
                    End With
                Case enumTableType.WRDBData
                    'each file is treated like a different table
                    For Each StaID As String In _dsData.StationIDs
                        lstStations.Add(String.Format("{0}", StaID))
                    Next
                Case enumTableType.Calibration
                    If BranchName = "All Branches" Or BranchName = "" Then
                        For Each b As clsBranch In Calibration.dictBranches.Values
                            For Each s As String In b.dictStations.Keys
                                If Not lstStations.Contains(s) Then lstStations.Add(s)
                            Next
                        Next
                    Else
                        With Calibration.dictBranches(BranchName)
                            For Each s As String In .dictStations.Keys
                                lstStations.Add(s)
                            Next
                        End With
                    End If
                Case Else
                    If WRDBProject IsNot Nothing Then
                        _WRDBProject.ResetPDox()
                        If BranchName = "All Branches" Or BranchName = "" Then
                            dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT Station_ID FROM {0} ORDER BY Station_ID", _WRDBProject.DB.AddBrackets(_TableName.Replace("'", "")))
                        Else
                            If Not _WRDBProject.CopyPDoxSupportTables(WRDB.Project.clsProject.enumTableTypes.Working, Project.clsProject.enumTableTypes.Stations) Then Return Nothing
                            dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT Dta.Station_ID FROM {0} Dta,Stations WHERE Dta.Station_ID=Stations.Station_ID AND Stations.Branch_ID='{1}' ORDER BY Dta.Station_ID", _WRDBProject.DB.AddBrackets(_TableName), BranchName)
                        End If

                        With dr
                            While dr IsNot Nothing AndAlso .Read
                                If Not .IsDBNull(0) Then
                                    Dim ID As String = .GetString(0)
                                    If StationSupport.ContainsKey(ID) Then ID = String.Format("{0} - {1}", ID, StationSupport(ID).Name)
                                    lstStations.Add(ID)
                                End If
                            End While
                        End With
                    End If
            End Select

            Return lstStations
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Get list of stations in this table having data with the specified pcode
    ''' </summary>
    Friend Function GetStationListByPCode(ByVal PCode As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Dim lstStations As New Generic.List(Of String)
            If _TableType = enumTableType.BMD Then
                'each segment is treated like a different table
                With _dsBMD
                    For i As Integer = 0 To .NumSegments - 1
                        Dim ID As String = .SegmentName(i)
                        lstStations.Add(String.Format("{0} - {1}", ID, StationSupport(ID).Name))
                    Next
                End With

            ElseIf _TableType = enumTableType.WRDBData Then
                'each file is treated like a different table
                For Each StaID As String In _dsData.StationIDs
                    lstStations.Add(String.Format("{0}", StaID))
                Next

            ElseIf _TableType = enumTableType.Calibration Then
                For Each b As clsBranch In Calibration.dictBranches.Values
                    For Each sta As clsStation In b.dictStations.Values
                        If sta.dictParameters.ContainsKey(PCode) AndAlso Not lstStations.Contains(sta.Name) Then lstStations.Add(sta.Name)
                    Next
                Next

            ElseIf WRDBProject IsNot Nothing Then
                _WRDBProject.ResetPDox()
                dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT Station_ID FROM {0} WHERE PCode = '{1}' ORDER BY Station_ID", _TableName.Replace("'", ""), StringSplit(PCode, " - ")(0))

                With dr
                    While dr IsNot Nothing AndAlso .Read
                        If Not .IsDBNull(0) Then
                            Dim ID As String = .GetString(0)
                            If StationSupport.ContainsKey(ID) Then ID = String.Format("{0} - {1}", ID, StationSupport(ID).Name)
                            lstStations.Add(ID)
                        End If
                    End While
                End With
            End If
            Return lstStations
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Retrieve range of available dates regardless of station or parameter
    ''' </summary>
    ''' <param name="MinDate">Minimum Date</param>
    ''' <param name="MaxDate">Maximum Date</param>
    ''' <returns>True if successful</returns>
    ''' <remarks></remarks>
    Friend Function GetDateRange(ByRef MinDate As Date, ByRef MaxDate As Date) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            MinDate = Date.MaxValue
            MaxDate = Date.MinValue

            If _TableType = enumTableType.BMD Then
                With _dsBMD
                    MinDate = .Time(0)
                    MaxDate = .Time(.NumTimes - 1)
                End With

            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    While .Read
                        If .Items(1) < MinDate Then MinDate = .Items(1)
                        If .Items(1) > MaxDate Then MaxDate = .Items(1)
                    End While
                End With

            ElseIf _TableType = enumTableType.Calibration Then
                With Calibration
                    If Not .tblSimulated.GetDateRange(MinDate, MaxDate) Then Return False
                    Dim MinDate2, MaxDate2 As Date
                    If Not .tblMeasured.GetDateRange(MinDate2, MaxDate2) Then Return False
                    If MinDate2 < MinDate Then MinDate = MinDate2
                    If MaxDate2 > MaxDate Then MaxDate = MaxDate2
                End With

            Else
                dr = _WRDBProject.DB.ExecuteReader("SELECT MIN(Date_Time) AS MINDATE, MAX(Date_Time) AS MAXDATE FROM {0}", _TableName)
                If dr IsNot Nothing AndAlso dr.Read Then
                    If Not dr.IsDBNull(0) Then MinDate = dr.GetValue(0)
                    If Not dr.IsDBNull(1) Then MaxDate = dr.GetValue(1)
                End If
            End If
            Return True
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Retrieve range of available depths regardless of station or parameter; null depth fields will return Double.MinValue/MaxValue
    ''' </summary>
    ''' <param name="MinDepth">Minimum Depth</param>
    ''' <param name="MaxDepth">Maximum Depth</param>
    ''' <returns>True if successful and has depth fields</returns>
    ''' <remarks></remarks>
    Friend Function GetDepthRange(ByRef MinDepth As Double, ByRef MaxDepth As Double) As Boolean
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            MinDepth = Double.MaxValue
            MaxDepth = Double.MinValue

            If _TableType = enumTableType.BMD Then
                Return False

            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    If .ContainsFieldName("Depth_M") Then
                        While .Read
                            If Val(.Items("Depth_M")) < MinDepth Then MinDepth = .Items("Depth_M")
                            If Val(.Items("Depth_M")) > MaxDepth Then MaxDepth = .Items("Depth_M")
                        End While
                    Else
                        Return False
                    End If
                End With

            ElseIf _TableType = enumTableType.Calibration Then
                With Calibration
                    If Not .tblSimulated.GetDepthRange(MinDepth, MaxDepth) Then Return False
                    Dim MinDepth2, MaxDepth2 As Double
                    If Not .tblMeasured.GetDepthRange(MinDepth2, MaxDepth2) Then Return False
                    If MinDepth2 < MinDepth Then MinDepth = MinDepth2
                    If MaxDepth2 > MaxDepth Then MaxDepth = MaxDepth2
                End With

            Else
                Try
                    dr = _WRDBProject.DB.ExecuteReader("SELECT MIN(Depth_M) AS MINDEPTH, MAX(Depth_M) AS MAXDEPTH FROM {0} WHERE Depth_M IS NOT NULL", _TableName)
                    If dr IsNot Nothing AndAlso dr.Read Then
                        If Not dr.IsDBNull(0) Then MinDepth = dr.GetValue(0)
                        If Not dr.IsDBNull(1) Then MaxDepth = dr.GetValue(1)
                    End If
                    dr.Close()
                    dr = Nothing
                    Dim numNull As Integer = _WRDBProject.DB.ExecuteScalar("SELECT COUNT(*) FROM {0} WHERE Depth_M IS NULL", _TableName)
                    If numNull > 0 Then
                        MinDepth = Math.Min(0, MinDepth)
                        MaxDepth = Math.Max(0, MaxDepth)
                    End If
                Catch ex As Exception
                    Return False
                End Try
            End If
            'if no depths found, return max range
            If MinDepth = Double.MaxValue Then MinDepth = Double.MinValue
            If MaxDepth = Double.MinValue Then MaxDepth = Double.MaxValue
            Return True
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return False
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            If dr IsNot Nothing AndAlso Not dr.IsClosed Then dr.Close()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Get list of dates associated with all specified station ids (and optionally pcode)
    ''' </summary>
    Friend Function GetDateList(ByVal StationIDs() As String, Optional ByVal PCode As String = "") As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            Dim lstDates As New Generic.List(Of String)

            If _TableType = enumTableType.BMD Then
                With _dsBMD
                    For i As Integer = 0 To .NumTimes - 1
                        lstDates.Add(.Time(i).ToString(MyDateTimeFormat))
                    Next
                End With

            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    .ActiveStationID = StringSplit(StationIDs(0), " - ")(0)
                    While .Read
                        lstDates.Add(.Items(1))
                    End While
                End With

            ElseIf _TableType = enumTableType.Calibration Then
                With Calibration
                    lstDates = .tblSimulated.GetDateList(StationIDs, PCode)
                    For Each dt As Date In .tblMeasured.GetDateList(StationIDs, PCode)
                        If Not lstDates.Contains(dt) Then lstDates.Add(dt)
                    Next
                    lstDates.Sort()
                End With

            Else
                Dim stalist As String = ""
                For Each s As String In StationIDs
                    s = StringSplit(s, " - ")(0)
                    stalist &= IIf(stalist = "", "", ",") & "'" & s & "'"
                Next

                Dim pcodeclause As String = ""
                If PCode <> "" Then pcodeclause = String.Format(" AND PCode='{0}'", PCode)
                dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT Date_Time FROM {0} WHERE Station_ID IN ({1}) {2} ORDER BY Date_Time", _TableName, stalist, pcodeclause)

                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then lstDates.Add(dr.GetDateTime(0).ToString(MyDateTimeFormat))
                End While
            End If
            Return lstDates
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Get list of dates associated with all specified station id and pcode
    ''' </summary>
    Friend Function GetDateList(ByVal StationID As String, ByVal PCode As String) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            Application.UseWaitCursor = True
            Application.DoEvents()
            Dim lstDates As New Generic.List(Of String)

            If _TableType = enumTableType.BMD Then
                With _dsBMD
                    For i As Integer = 0 To .NumTimes - 1
                        lstDates.Add(.Time(i).ToString(MyDateTimeFormat))
                    Next
                End With

            ElseIf _TableType = enumTableType.WRDBData Then
                With _dsData
                    .ActiveStationID = StringSplit(StationID, " - ")(0)
                    While .Read
                        lstDates.Add(.Items(1))
                    End While
                End With

            ElseIf _TableType = enumTableType.Calibration Then
                With Calibration
                    lstDates = .tblSimulated.GetDateList(StationID, PCode)
                    For Each dt As Date In .tblMeasured.GetDateList(StationID, PCode)
                        If Not lstDates.Contains(dt) Then lstDates.Add(dt)
                    Next
                    lstDates.Sort()
                End With

            Else
                dr = _WRDBProject.DB.ExecuteReader("SELECT DISTINCT Date_Time FROM {0} WHERE Station_ID = '{1}' AND PCode = '{2}' ORDER BY Date_Time", _TableName, StationID, PCode)

                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then lstDates.Add(dr.GetDateTime(0).ToString(MyDateTimeFormat))
                End While
            End If
            Return lstDates
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return Nothing
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            If _WRDBProject IsNot Nothing Then _WRDBProject.DB.CloseConnection()
            Application.UseWaitCursor = False
        End Try
    End Function

    ''' <summary>
    ''' Acquire data for specified Station and PCode from source table and return in DataTable having two columns: Date_Time and Result; 
    ''' Note that Result may be modified based on Criteria table and NumExceed, etc., stats will be updated (if is WRDB table);
    ''' Note that optional parameters are ignored unless is query against WRDB 5.0 table that contains ALL fields
    ''' </summary>
    ''' <param name="StationID">Desired station ID</param>
    ''' <param name="PCode">Desired PCode</param>
    ''' <param name="MinDate">Minimum date (if omit, all dates included)</param>
    ''' <param name="MaxDate">Maximum date</param>
    ''' <param name="MinDepth">Minimum allowable depth (note: NULL depths in table are assigned depth of 0.0)</param>
    ''' <param name="MaxDepth">Maximum allowable depth</param>
    ''' <param name="MinLEW">Minimum allowable width (note: NULL depths in table are assigned depth of 0.0)</param>
    ''' <param name="MaxLEW">Maximum allowable width</param>
    ''' <param name="CCodeList">List of desired CCodes to include; if empty list or is Nothing, all CCodes will be included</param>
    Friend Function GetResults(StationID As String, PCode As String,
                               Optional MinDate As Date = #1/1/1000#,
                               Optional MaxDate As Date = #1/1/1000#,
                               Optional MinDepth As Double = Double.MinValue,
                               Optional MaxDepth As Double = Double.MaxValue,
                               Optional MinLEW As Double = Double.MinValue,
                               Optional MaxLEW As Double = Double.MaxValue,
                               Optional CCodeList As Generic.List(Of String) = Nothing,
                               Optional LCodeList As Generic.List(Of String) = Nothing,
                               Optional SCodeList As Generic.List(Of String) = Nothing,
                               Optional QCodeList As Generic.List(Of String) = Nothing) As DataTable
        Try
            NumExceed = 0
            NumNonCalc = 0
            NumNonDetect = 0

            If MinDate = #1/1/1000# Or MinDate = Date.MinValue Then MinDate = Now.AddYears(-100)
            If MaxDate = #1/1/1000# Or MaxDate = Date.MaxValue Then MaxDate = Now.AddYears(100)

            Dim dtResult As New DataTable()
            dtResult.Columns.Add("Date_Time", GetType(Date))
            dtResult.Columns.Add("Result", GetType(Double))

            If TableType = clsTable.enumTableType.BMD Then
                With dsBMD
                    'cannot interpret depths, so ignore
                    Dim dt() As Date = .Times
                    Dim conc() As Single = Nothing
                    Try
                        conc = .Conc(StationID, PCode)
                    Catch ex As Exception
                        'invalid station id or pcode cause error; just return empty series
                        Return Nothing
                    End Try
                    For i As Integer = 0 To dt.Length - 1
                        If conc(i) <> Double.NaN Then
                            dtResult.Rows.Add(dt(i), conc(i))
                        End If
                    Next
                End With

            ElseIf TableType = clsTable.enumTableType.WRDBData Then
                With dsData
                    .ActiveStationID = StationID
                    Dim concItem As Integer = -1
                    For i As Integer = 2 To .FieldNames.Count - 1
                        If .FieldNames(i).Split(" ")(0) = PCode Or .FieldNames(i) = PCode Then concItem = i : Exit For
                    Next
                    If concItem = -1 Then concItem = .FieldNames.IndexOf("Result")
                    If concItem = -1 Then Throw New ApplicationException("Unable to reference field for selected PCode: " & PCode)

                    Dim depthItem As Integer = .FieldIndex("Depth_M")

                    If depthItem = -1 Then
                        dtResult = .GetDataTable(StationID, PCode)
                    Else
                        Dim dtPrev As Date = Date.MinValue
                        While .Read
                            Dim dt As Date = CDate(.Items("Date_Time"))
                            Dim conc As Double = .Items(concItem)
                            If MinDepth <> Double.MinValue AndAlso depthItem <> -1 Then
                                Dim depth As String = .Items(depthItem)
                                If IsNumeric(depth) AndAlso Val(depth) >= MinDepth AndAlso Val(depth) <= MaxDepth Then
                                    dtResult.Rows.Add(dt, conc)
                                End If
                            Else
                                dtResult.Rows.Add(dt, conc)
                            End If
                        End While
                    End If

                End With

            ElseIf TableType = clsTable.enumTableType.Calibration Then
                WarningMsg("Invalid table type has been passed to GetResults!")

            ElseIf Not IsConvertedDB AndAlso TableType = clsTable.enumTableType.Folder AndAlso IO.Path.GetExtension(TableFile).ToUpper = ".TXT" Then
                'because date format may be odd (yyyy-mm-dd HH:mm) built-in provider may not recognize; use datasource instead

                Dim ds As New WRDB.DataSource.clsDataText

                With ds
                    .Initialize(Database_Directory & "\" & TableFile)

                    Dim depthItem As Integer = .FieldIndex("Depth_M")
                    Dim lewItem As Integer = .FieldIndex("LEW_Pct")
                    Dim ccodeItem As Integer = .FieldIndex("CCode")
                    Dim lcodeItem As Integer = .FieldIndex("LCode")
                    Dim scodeItem As Integer = .FieldIndex("SCode")
                    Dim qcodeItem As Integer = .FieldIndex("QCode")

                    While .Read
                        If Not (.Items("Station_ID") = StationID AndAlso .Items("PCode") = PCode) Then Continue While
                        Dim dt As Date = Date.MinValue
                        If Not (Date.TryParse(.Items("Date_Time"), dt) AndAlso dt >= MinDate AndAlso dt <= MaxDate) Then Continue While
                        If ccodeItem <> -1 AndAlso CCodeList IsNot Nothing AndAlso CCodeList.Count > 0 AndAlso Not CCodeList.Contains(.Items(ccodeItem)) Then Continue While
                        If lcodeItem <> -1 AndAlso LCodeList IsNot Nothing AndAlso LCodeList.Count > 0 AndAlso Not LCodeList.Contains(.Items(lcodeItem)) Then Continue While
                        If scodeItem <> -1 AndAlso SCodeList IsNot Nothing AndAlso SCodeList.Count > 0 AndAlso Not SCodeList.Contains(.Items(scodeItem)) Then Continue While
                        If qcodeItem <> -1 AndAlso QCodeList IsNot Nothing AndAlso QCodeList.Count > 0 AndAlso Not QCodeList.Contains(.Items(qcodeItem)) Then Continue While
                        If depthItem <> -1 AndAlso MinDepth <> Double.MinValue AndAlso Not (IsNumeric(.Items(depthItem)) AndAlso .Items(depthItem) >= MinDepth AndAlso .Items(depthItem) <= MaxDepth) Then Continue While
                        If lewItem <> -1 AndAlso MinLEW <> Double.MinValue AndAlso Not (IsNumeric(.Items(lewItem)) AndAlso .Items(lewItem) >= MinLEW AndAlso .Items(lewItem) <= MaxLEW) Then Continue While

                        'all filter criteria have passed
                        Dim conc As Double
                        If GetResult(StationID, PCode, dt, .Items("RCode"), .Items("Result"), conc) Then dtResult.Rows.Add(dt, conc)
                    End While
                    .Close()
                    .Dispose()
                End With

            Else 'WRDB table
                Dim HasBlob As Boolean = False
                Dim HasAllFields As Boolean = True

                'see if data table has blob (and other) fields
                Dim lstFields As Generic.List(Of String) = WRDBProject.DB.GetColumnList(TableName)

                HasBlob = lstFields.Contains("OBJECT_TYPE") AndAlso lstFields.Contains("OBJECT_VALUE")
                HasAllFields = lstFields.Contains("DEPTH_M") AndAlso
                               lstFields.Contains("LEW_PCT") AndAlso
                               lstFields.Contains("CCODE") AndAlso
                               lstFields.Contains("LCODE") AndAlso
                               lstFields.Contains("SCODE") AndAlso
                               lstFields.Contains("QCODE")

                'Dim drSchema As DbDataReader = WRDBProject.DB.ExecuteReader("SELECT * FROM " & WRDBProject.DB.AddBrackets(TableName), CommandBehavior.SchemaOnly)
                'Dim dtSchema As DataTable = drSchema.GetSchemaTable
                'drSchema.Close()
                'WRDBProject.DB.CloseConnection()

                'If dtSchema IsNot Nothing Then
                '    For Each r As DataRow In dtSchema.Rows
                '        Select Case r("ColumnName").ToString.ToUpper
                '            Case "OBJECT_TYPE" : HasBlob = True
                '            Case "DEPTH_M" : HasDepth = True
                '            Case "LEW_PCT" : HasLEW = True
                '            Case "CCODE" : HasCCode = True
                '            Case "LCODE" : HasLCode = True
                '            Case "SCODE" : HasSCode = True
                '            Case "QCODE" : HasQCode = True
                '        End Select
                '    Next
                'End If

                Dim AddedFields As String = ""
                If HasAllFields Then AddedFields &= ",Depth_M,LEW_Pct,CCode,LCode,SCode,QCode"
                If HasBlob Then AddedFields &= ",Object_Type,Object_Value"

                Dim dr As DbDataReader = WRDBProject.DB.ExecuteReader("SELECT Date_Time,RCode,Result{0} FROM {1} WHERE Station_ID='{2}' AND PCode='{3}' AND Date_Time BETWEEN {4} AND {5} ORDER BY Date_Time",
                                                                       AddedFields, WRDBProject.DB.AddBrackets(TableName),
                                                                       StationID, PCode,
                                                                       WRDBProject.DB.DateTime(MinDate), WRDBProject.DB.DateTime(MaxDate))
                If dr Is Nothing Then Return Nothing
                With dr
                    While .Read
                        If .IsDBNull(0) Then Continue While
                        Dim dt As DateTime = CDate(.GetValue(0))
                        Dim RCode As String = TestNull(.GetValue(1), "")
                        Dim Result As Object = .GetValue(2)
                        Dim Data() As Byte = Nothing
                        Dim NumRec As Integer = 0
                        Dim BaseDate As Date = dt
                        Dim Depth As Double = 0.0
                        Dim LEWPct As Double = 0.0
                        Dim CCode As String = ""
                        Dim LCode As String = ""
                        Dim SCode As String = ""
                        Dim QCode As String = ""

                        If HasAllFields AndAlso HasBlob Then
                            Dim ObjName As String = TestNull(.GetValue(9), "")
                            If clsBlob.ObjectType(ObjName) = clsBlob.enumObjectType.TimeSeries AndAlso Not .IsDBNull(10) Then
                                Data = .GetValue(10)
                                NumRec = Data.Length / 8
                            End If
                        End If

                        Dim conc As Double
                        For RecNum As Integer = 0 To IIf(HasBlob, NumRec - 1, 0)
                            If HasBlob Then
                                dt = BaseDate.AddSeconds(BitConverter.ToInt32(Data, RecNum * 8))
                                conc = BitConverter.ToSingle(Data, RecNum * 8 + 4)
                            ElseIf HasAllFields Then
                                Depth = TestNull(.GetValue(3), 0.0)
                                LEWPct = TestNull(.GetValue(4), 0.0)
                                CCode = TestNull(.GetValue(5), "")
                                LCode = TestNull(.GetValue(6), "")
                                SCode = TestNull(.GetValue(7), "")
                                QCode = TestNull(.GetValue(8), "")
                                If Depth < MinDepth OrElse Depth > MaxDepth Then Continue While
                                If LEWPct < MinLEW OrElse LEWPct > MaxLEW Then Continue While
                                If CCodeList IsNot Nothing AndAlso CCodeList.Count > 0 AndAlso Not CCodeList.Contains(CCode) Then Continue While
                                If LCodeList IsNot Nothing AndAlso LCodeList.Count > 0 AndAlso Not LCodeList.Contains(LCode) Then Continue While
                                If SCodeList IsNot Nothing AndAlso SCodeList.Count > 0 AndAlso Not SCodeList.Contains(SCode) Then Continue While
                                If QCodeList IsNot Nothing AndAlso QCodeList.Count > 0 AndAlso Not QCodeList.Contains(QCode) Then Continue While
                                If Not GetResult(StationID, PCode, dt, RCode, Result, conc) Then Continue While
                            Else
                                If Not GetResult(StationID, PCode, dt, RCode, Result, conc) Then Continue While
                            End If
                            dtResult.Rows.Add(dt, conc)
                        Next
                    End While
                    .Close()
                    dr = Nothing
                End With
            End If
            Return dtResult
        Catch ex As Exception
            ErrorMsg(, ex)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' This will interpret rcode and adjust result if is non detect; will also increment counters; returns False if is NonCalc value which should not be added
    ''' </summary>
    ''' <param name="StaID">Station ID associated with observation (will test for null value)</param>
    ''' <param name="DateTime">Date-time associated with observation (will test for null value)</param>
    ''' <param name="PCode">PCode associated with observation (will test for null value)</param>
    ''' <param name="RCode">RCode associated with observation (will test for null value)</param>
    ''' <param name="Result">Numerical observation  (will test for null value)</param>
    ''' <param name="NewResult">Updated value depending on RCode and PCode</param>
    ''' <returns>Will return False only if unable to compute Result (not if criteria could not be found)</returns>
    Friend Function GetResult(StaID As Object, PCode As Object, DateTime As Date, RCode As Object, Result As Object, ByRef NewResult As Double) As Boolean
        StaID = TestNull(StaID, "")
        PCode = TestNull(PCode, "")
        DateTime = TestNull(DateTime, CDate("1/1/1900"))
        RCode = TestNull(RCode, "")
        Result = TestNull(Result, Double.MinValue)
        NewResult = Result 'will return result value unless rcode is a non-detect value
        Dim MinCrit, MaxCrit As Double, IsNonDetect As Boolean = False, IsCalcStats As Boolean = True

        'note: Criteria class instantiated in clsTable when tablename is assigned

        Dim CritFound As Boolean = Criteria IsNot Nothing AndAlso Criteria.TryGetCriteria(StaID, PCode, DateTime, RCode, NewResult, MinCrit, MaxCrit, , IsNonDetect, IsCalcStats)

        If RCode = "" And Result = Double.MinValue Then 'null result and empty rcode--treat as non-calc value
            NumNonCalc += 1
            Return False
        ElseIf CritFound Then
            If NewResult < MinCrit Or NewResult > MaxCrit Then NumExceed += 1
            If IsNonDetect Then NumNonDetect += 1
            Return True
        ElseIf IsCalcStats Then
            If IsNonDetect Then NumNonDetect += 1
            Return True
        Else
            NumNonCalc += 1
            Return False
        End If
    End Function

    Private Function IsReservedField(ByVal FieldName As String) As Boolean
        For i As Integer = enumBMDField.BranchNum To enumBMDField.RiverMile
            If String.Equals(FieldName, BMDFieldName(i), StringComparison.OrdinalIgnoreCase) Then
                BMDFieldName(i) = FieldName 'to force match of case
                Return True
            End If
        Next
        Return False
    End Function

    ''' <summary>
    ''' Returns name of data source (and table if applicable) suitable for display in list
    ''' </summary>
    Public Overrides Function ToString() As String
        If _TableType = enumTableType.Folder Then
            If IsConvertedDB Then
                Return _TableFile.Replace(".TXT", ".DB")
            Else
                Return _TableFile
            End If
        ElseIf _TableType = enumTableType.Database Then
            Return IO.Path.GetFileName(_DataSource) & " [" & RemoveBrackets(_TableName) & "]"
        ElseIf _TableType = enumTableType.BMD Then
            Return _TableFile
        ElseIf _TableType = enumTableType.WRDBData Then
            Select Case IO.Path.GetExtension(_DataSource).ToUpper
                Case ".DSS"
                    Return IO.Path.GetFileName(_DataSource) & " [" & RemoveBrackets(_TableName) & "]"
                Case Else
                    Return _DataSource
            End Select
        ElseIf _TableType = enumTableType.Calibration Then
            Return "Calibration: " & Calibration.Name
        ElseIf _ProjectName IsNot Nothing Then 'is WRDB project
            Dim tbl As String = RemoveBrackets(_TableName)
            If tbl.StartsWith("TEMP_", StringComparison.OrdinalIgnoreCase) Then tbl = "Selected Data"
            Return String.Format("{0} [{1}]", _ProjectName, tbl)
        Else
            Return RemoveBrackets(_TableName)
        End If
    End Function

    ''' <summary>
    ''' Collection of branch names associated with BMD branches; must be placed into .Graph (ini) file so is avaiable to this program
    ''' </summary>
    Friend Property BranchName() As Generic.List(Of String)
        Get
            Return _BranchName
        End Get
        Set(ByVal value As Generic.List(Of String))
            _BranchName = value
        End Set
    End Property

    ''' <summary>
    ''' The filename for database, foldername for file, or WRDB projectname
    ''' </summary>
    Public Property DataSource() As String
        Get
            Return _DataSource
        End Get
        Set(ByVal value As String)
            _DataSource = value
        End Set
    End Property

    ''' <summary>
    ''' Get reference to BMD data source
    ''' </summary>
    Friend Property dsBMD() As WRDB.DataSource.clsBMD
        Get
            Return _dsBMD
        End Get
        Set(ByVal value As WRDB.DataSource.clsBMD)
            _dsBMD = value
        End Set
    End Property

    ''' <summary>
    ''' Get reference to WRDB data source
    ''' </summary>
    Public Property dsData() As WRDB.DataSource.clsData
        Get
            Return _dsData
        End Get
        Set(ByVal value As WRDB.DataSource.clsData)
            _dsData = value
        End Set
    End Property

    ''' <summary>
    ''' WRDB5 Project name (may be temporary project if non-WRDB5)
    ''' </summary>
    Public Property ProjectName() As String
        Get
            Return TestNull(_ProjectName, "OTHER_" & _DataSource)
        End Get
        Set(ByVal value As String)
            _ProjectName = value
        End Set
    End Property

    ''' <summary>
    ''' Table name if is from WRDB project or filename of non-WRDB data file (with extension)
    ''' </summary>
    Public Property TableFile() As String
        Get
            Return _TableFile
        End Get
        Set(ByVal value As String)
            _TableFile = value
        End Set
    End Property

    ''' <summary>
    ''' Directory if BMD file or folder-based database file; otherwise if name of database or WRDB project name
    ''' </summary>
    Friend ReadOnly Property Database_Directory() As String
        Get
            Select Case _TableType
                Case enumTableType.BMD
                    Return IO.Path.GetDirectoryName(_DataSource)
                Case Else
                    Return _DataSource
            End Select
        End Get
    End Property

    ''' <summary>
    ''' Table name if is from WRDB project or filename of non-WRDB data file
    ''' Rename table filename to be compatible with queries
    ''' </summary>
    Public Property TableName() As String
        Get
            Return TestNull(_TableName, _TableFile)
        End Get
        Set(ByVal value As String)
            _TableName = value
            Select Case _TableType
                Case enumTableType.WRDB4, enumTableType.WRDB5
                    Criteria = New WRDB.Project.clsCriteria(_WRDBProject, _TableName)
                Case enumTableType.WRDBData
                    _dsData.ActiveTable = _TableName
            End Select
        End Set
    End Property

    ''' <summary>
    ''' Describes source of table (if WRDB5 or 4, can expect support tables to be present)
    ''' </summary>
    Public ReadOnly Property TableType() As clsTable.enumTableType
        Get
            Return _TableType
        End Get
    End Property

    ''' <summary>
    ''' If table is based on data queried by WRDB, recreate this table again in case it has been overwritten or no longer exists
    ''' Feb 28, 2011; this adversely affects performance for small queries from large tables, requiring that queries be rerun
    ''' in Graph which can be slow; assume that if the temp table already exists, that it is valid and reuse it.
    ''' </summary>
    Friend Sub CreateTempData()
        If String.IsNullOrEmpty(SqlString) Then
            'the tablename contains all the records and no need to reselect
        ElseIf SqlString.Contains(" WHERE ") Then
            If WRDBProject.DB.TableExists(TableName) Or WRDBProject.DB.ViewExists(TableName) Then
                'selection was into temporary table, and it still exists so assume it is still valid
            Else
                'selection was into a view or the temporary table no longer exists; recreate it by sending to new temporary table
                With WRDBProject.DB
                    If .SupportsViews And False Then 'always create table rather than view for subsequent speed
                        .DropView(TableName)
                        .DropTable(TableName)
                        .ExecuteNonQuery("CREATE VIEW {0} AS {1}", TableName, SqlString)
                    Else
                        .DropView(TableName)
                        .DropTable(TableName)
                        WRDBProject.CreateTable(TableName)
                        .ExecuteNonQuery(String.Format("INSERT INTO {0} {1}", TableName, SqlString))
                    End If
                End With
            End If
        Else
            'all records selected from main table; extract original tablename from query (syntax is SELECT * FROM tblname WHERE ...)
            Dim OrigTableName As String = ""
            Dim ar() As String = SqlString.Split(" ")
            For i As Integer = 0 To ar.Length - 2
                If ar(i).ToUpper = "FROM" Then OrigTableName = ar(i + 1) : Exit For
            Next
            If OrigTableName <> "" Then TableName = RemoveBrackets(OrigTableName)
        End If
    End Sub

    ''' <summary>
    ''' If is WRDB5 or 4 project, this contains the structure
    ''' </summary>
    Public ReadOnly Property WRDBProject() As WRDB.Project.clsProject
        Get
            Return _WRDBProject
        End Get
    End Property

    Public Sub SaveSettings(iniFile As WRDB.IniFile.clsIniFile, SectionName As String)
        With Me
            iniFile.SetKeyText(SectionName, "TableType", .TableType)
            iniFile.SetKeyText(SectionName, "ProjectName", .ProjectName)
            If Not String.IsNullOrEmpty(.DataSource) Then iniFile.SetKeyText(SectionName, "RelativePath", GetRelativePath(IO.Path.GetDirectoryName(iniFile.FileName), .DataSource))
            Select Case .TableType
                Case clsTable.enumTableType.WRDB5
                    iniFile.SetKeyText(SectionName, "SqlString", .SqlString)
                Case clsTable.enumTableType.WRDB4
                Case clsTable.enumTableType.BMD, clsTable.enumTableType.WRDBData
                    iniFile.SetKeyText(SectionName, "DataSource", .DataSource)
                Case clsTable.enumTableType.Calibration
                    .Calibration.SaveSettings(iniFile, SectionName)
                Case clsTable.enumTableType.Database
                    iniFile.SetKeyText(SectionName, "DataSource", .DataSource)
                    iniFile.SetKeyText(SectionName, "TableFile", .TableFile)
                Case enumTableType.External
                    iniFile.SetKeyText(SectionName, "DataSource", ExternalData.ConnectionName)
                    iniFile.SetKeyText(SectionName, "TableFile", ExternalData.TableConnectionName)
                Case Else
                    If IsConvertedDB Then
                        iniFile.SetKeyText(SectionName, "DataSource", .DataSourceDB)
                        iniFile.SetKeyText(SectionName, "TableFile", .TableFile.Replace(".TXT", ".DB"))
                    Else
                        iniFile.SetKeyText(SectionName, "DataSource", .DataSource)
                        iniFile.SetKeyText(SectionName, "TableFile", .TableFile)
                    End If
            End Select
            iniFile.SetKeyText(SectionName, "TableName", .TableName)
        End With
    End Sub

    Public Sub LoadSettings(iniFile As WRDB.IniFile.clsIniFile, SectionName As String)
        Dim TableType As enumTableType = iniFile.GetKeyText(SectionName, "TableType", enumTableType.WRDB5)
        Dim ProjectName As String = iniFile.GetKeyText(SectionName, "ProjectName")
        Dim RelPath As String = iniFile.GetKeyText(SectionName, "RelativePath")
        DataSource = iniFile.GetKeyText(SectionName, "DataSource")
        TableFile = iniFile.GetKeyText(SectionName, "TableFile")
        Select Case TableType
            Case clsTable.enumTableType.WRDB5
                SqlString = iniFile.GetKeyText(SectionName, "SqlString")
                CreateWRDB5Table(ProjectName)
            Case clsTable.enumTableType.WRDB4
                CreateWRDB4Table(ProjectName)
            Case clsTable.enumTableType.BMD
                CreateBMDTable(DataSource, iniFile.FileName)
            Case clsTable.enumTableType.WRDBData
                CreateWRDBDataTable(DataSource, iniFile.FileName)
            Case clsTable.enumTableType.Calibration
                Calibration = New clsCalibration("")
                Calibration.LoadSettings(iniFile, SectionName)
            Case clsTable.enumTableType.Database, enumTableType.Folder
                CreateDatabaseTable(DataSource, TableFile, iniFile.FileName)
            Case enumTableType.External
                CreateExternalTable(iniFile.GetKeyText(SectionName, "ConnectionName"), iniFile.GetKeyText(SectionName, "TableConnection"))
            Case Else
                Throw New ApplicationException("Invalid TableType encountered: " & TableType)
        End Select
        TableName = iniFile.GetKeyText(SectionName, "TableName")
    End Sub

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: free other state (managed objects).
            End If

            ' TODO: free your own state (unmanaged objects).
            ' TODO: set large fields to null.
            If StationSupport IsNot Nothing Then StationSupport.Clear()
            StationSupport = Nothing
            If PCodeSupport IsNot Nothing Then PCodeSupport.Clear()
            PCodeSupport = Nothing
            If _BranchName IsNot Nothing Then _BranchName.Clear()
            _BranchName = Nothing
            If _dsBMD IsNot Nothing Then _dsBMD.Close()
            _dsBMD = Nothing
            If _WRDBProject IsNot Nothing Then
                With _WRDBProject
                    If .DataSet IsNot Nothing Then .DataSet.Dispose()
                    If .dsWRDB IsNot Nothing Then .dsWRDB.Dispose()
                    .DB.Connection.Close()
                    .DB.Connection.Dispose()
                End With
                _WRDBProject = Nothing
            End If
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

    Private Sub _dsBMD_RevertingToDiskBased() Handles _dsBMD.RevertingToDiskBased
        If False AndAlso Not _ExpertMode Then
            If WarningMsg("WRDB Graph has run out of memory trying to load the BMD file and will automatically revert to disk-based mode (rather than in-memory mode); this will cause decreased performance.\n\nContinue reading BMD file using disk-based mode?") = DialogResult.Cancel Then
                _dsBMD.InRam = True
                Throw New Exception("User has cancelled loading of large BMD file.")
            End If
        End If
    End Sub

    ''' <summary>
    ''' Instantiate class, setting some global variables
    ''' </summary>
    ''' <param name="GroupFileName">Name of graph group file (or any file which indicates alternate directory to search tables for)</param>
    ''' <param name="ExpertMode">True if want to suppress warnings</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal GroupFileName As String, ByVal ExpertMode As Boolean)
        _GroupFileName = GroupFileName
        _ExpertMode = ExpertMode
    End Sub

    ''' <summary>
    ''' Instantiate class (must call Initialize to set some global variables)
    ''' </summary>
    Public Sub New()
    End Sub

    ''' <summary>
    ''' Initialize class, setting some global variables (used for Interface)
    ''' </summary>
    ''' <param name="GroupFileName">Name of graph group file (or any file which indicates alternate directory to search tables for)</param>
    ''' <param name="ExpertMode">True if want to suppress warnings</param>
    ''' <remarks></remarks>
    Public Sub Initialize(ByVal GroupFileName As String, ByVal ExpertMode As Boolean)
        _GroupFileName = GroupFileName
        _ExpertMode = ExpertMode
    End Sub
End Class
